﻿using System;
using System.Collections.Generic;
using System.Linq;
using NetSurveillance.WebApp.DataServices.Models;
using ServiceStack.OrmLite;

namespace NetSurveillance.WebApp.DataServices.Implementations
{
    public class DataRecordService : BaseService<DataRecord>
    {
        public DataRecordService(string connStr, IOrmLiteDialectProvider provider = null)
            : base(connStr, provider)
        {
        }

        public IList<string> GetEmailList(long taskId, string protocol = null)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"Select distinct ""MailFrom"" from ""DataRecords"" 
                        where ""StatusToken"" != 0 and ""MailFrom"" is not null 
                            and ""MailFrom"" != '' and ""TaskId""={0}";
                if (!string.IsNullOrWhiteSpace(protocol))
                    searchSql += @" and ""Protocol""={0}".Params(protocol);
                var emailList = db.GetList<string>(searchSql, taskId);
                return emailList;
            }
        }

        public IList<SensitiveAccount> GetAccountList(long taskId)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string searchSql =
                    @"select distinct SA.""Id"",SA.*,DR.""Protocol"" from ""DataRecords"" as DR
                        left join ""SensitiveAccounts"" as SA on DR.""SensitiveAccountId""=SA.""Id""
                        where DR.""StatusToken"" != 0 and DR.""TaskId""=:taskId
                        and DR.""SensitiveAccountId"" is not null 
                        order by SA.""Id"" desc";
                var accounts = db.SqlList<SensitiveAccount>(searchSql, new {taskId});
                return accounts;
            }
        }

        public IList<DataRecordResult> GetDataRecords(int pageIndex, int pageSize,
            DataRecordFilter drFilter,out int? totalCount)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select distinct DR.""Id"",Hst.""HostName"",Hst.""IP"" as ""HostIP"",Hst.""Mac"" as ""HostMac"",
                    Tsk.""Name"" as ""TaskName"",Cases.""Name"" as ""CaseName"",
                    SAcc.""Account"",SAcc.""Password"",DR.*,DRK.""HitName"" from ""DataRecords"" as DR 
                    left join ""Hosts"" as Hst on DR.""HostId""=Hst.""Id"" 
                    left join ""Tasks"" as Tsk on DR.""TaskId""=Tsk.""Id"" 
                    left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                    left join ""SensitiveAccounts"" as SAcc on DR.""SensitiveAccountId""=SAcc.""Id""
                    left join ""DataRecordKeywords"" as DRK on DR.""Id""=DRK.""DataRecordId""
                    where DR.""StatusToken"" != 0 ";
                string searchTotalCountSql =
                    @"select count(distinct DR.""Id"") from ""DataRecords"" as DR 
                        left join ""DataRecordKeywords"" as DRK on DR.""Id""=DRK.""DataRecordId""
                        left join ""Hosts"" as Hst on DR.""HostId""=Hst.""Id"" 
                        where DR.""StatusToken"" != 0 ";

                string whereSql =
                    GenerateWhere(drFilter);
                searchSql += whereSql;
                if (!string.IsNullOrWhiteSpace(drFilter.OrderField) &&
                    !string.IsNullOrWhiteSpace(drFilter.Order))
                    searchSql += string.Format(" order by \"{0}\" {1}", drFilter.OrderField, drFilter.Order);
                if (!drFilter.IsTakeAll)
                    searchSql += " limit :pageSize offset :skipCount";
                var dynamicParams = new
                    {
                        drFilter.TaskId, pageSize,
                        skipCount = pageSize*(pageIndex - 1),
                        drFilter.HostId, drFilter.Protocol,
                        drFilter.Email, drFilter.AccountId,
                        drFilter.KeywordId, drFilter.CaseId,
                        drFilter.Keyword, drFilter.HostMac
                    };
                var dataRecordList = db.SqlList<DataRecordResult>(searchSql, dynamicParams);
                searchTotalCountSql += whereSql;
                totalCount = db.QueryScalar<int?>(searchTotalCountSql, dynamicParams);
                return dataRecordList;
            }
        }

        private string GenerateWhere(DataRecordFilter drFilter)
        {
            string whereSql = string.Empty;
            if (drFilter.HostId.HasValue)
                whereSql += " and DR.\"HostId\" = :hostId";
            if (!string.IsNullOrWhiteSpace(drFilter.Protocol))
                whereSql += " and DR.\"Protocol\" = :protocol";
            if (drFilter.KeywordId.HasValue)
            {
                whereSql += drFilter.KeywordId.Value == -1
                                ? " and DRK.\"DataRecordId\" is not null"
                                : " and DRK.\"KeywordId\" = :keywordId";
            }
            if (!string.IsNullOrWhiteSpace(drFilter.Keyword))
            {
                whereSql +=
                    " and DRK.\"KeywordId\" in (select \"Id\" from \"Keywords\" where \"StatusToken\" != 0 and \"KeywordName\"=:keyword)";
            }
            if (!string.IsNullOrWhiteSpace(drFilter.Email))
            {
                whereSql += drFilter.Email == "-1"
                                ? " and DR.\"MailFrom\" is not null and trim(DR.\"MailFrom\") != ''"
                                : " and DR.\"MailFrom\" = :email";
            }
            if (drFilter.AccountId.HasValue)
            {
                whereSql += drFilter.AccountId.Value == -1
                                ? " and DR.\"SensitiveAccountId\" is not null"
                                : " and DR.\"SensitiveAccountId\" = :accountId";
            }
            if (drFilter.CaseId.HasValue || (drFilter.TaskId.HasValue && drFilter.TaskId.Value > 0))
            {
                whereSql += drFilter.CaseId.HasValue
                                 ? @" and DR.""TaskId"" in (select ""Id"" from ""Tasks"" where ""CaseId""=:caseId) "
                                 : @" and DR.""TaskId""=:taskId ";
            }
            if (!string.IsNullOrWhiteSpace(drFilter.HostMac))
                whereSql += " and Hst.\"Mac\" = :hostMac";
            if (!drFilter.IsTakeNoTitle)
                whereSql += " and ((DR.\"Title\" is not null and DR.\"Title\" != '') or DR.\"ActionCode\" is not null)";

            return whereSql;
        }

        public IList<DataRecordResult> GetDataRecords(long taskId, long maxRecordId,
                                                      DataRecordFilter drFilter = null)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select Hst.""HostName"",Hst.""IP"" as ""HostIP"",Hst.""Mac"" as ""HostMac"",
                        Tsk.""Name"" as ""TaskName"",Cases.""Name"" as ""CaseName"",
                        SAcc.""Account"",SAcc.""Password"",DR.* from ""DataRecords"" as DR 
                        left join ""Hosts"" as Hst on DR.""HostId""=Hst.""Id"" 
                        left join ""Tasks"" as Tsk on DR.""TaskId""=Tsk.""Id"" 
                        left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                        left join ""SensitiveAccounts"" as SAcc on DR.""SensitiveAccountId""=SAcc.""Id"" ";
                string whereSql =
                    @" where DR.""StatusToken"" != 0 and DR.""TaskId""=:taskId and DR.""Id"">:maxRecordId ";
                const string orderSql = @" order by DR.""Id"" Desc";
                dynamic dynamicParams = new {taskId, maxRecordId};
                if (drFilter != null)
                {
                    if (!string.IsNullOrWhiteSpace(drFilter.Keyword) ||
                        drFilter.KeywordId.HasValue)
                        searchSql +=
                            @" left join ""DataRecordKeywords"" as DRK on DR.""Id""=DRK.""DataRecordId"" ";
                    whereSql += GenerateWhere(drFilter);
                    dynamicParams = new
                        {
                            taskId,
                            maxRecordId,
                            drFilter.HostId,
                            drFilter.Protocol,
                            drFilter.Email,
                            drFilter.AccountId,
                            drFilter.KeywordId,
                            drFilter.CaseId,
                            drFilter.Keyword,
                            drFilter.HostMac
                        };
                }
                searchSql += whereSql + orderSql;
                var dataRecordList = db.SqlList<DataRecordResult>(searchSql, (object) dynamicParams);
                return dataRecordList;
            }
        }

        public IList<DataRecordResult> GetDataRecords(long[] hostIds, MultiSearch multiSearch,
                                                      int pageIndex, int pageSize, out int totalCount,
                                                      DataRecordFilter drFilter)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select distinct DR.""Id"",Hst.""HostName"",Hst.""IP"" as ""HostIP"",Hst.""Mac"" as ""HostMac"",
                    Tsk.""Name"" as ""TaskName"",Cases.""Name"" as ""CaseName"",
                    SAcc.""Account"",SAcc.""Password"",DR.*,DRK.""HitName"" from ""DataRecords"" as DR 
                    left join ""Hosts"" as Hst on DR.""HostId""=Hst.""Id"" 
                    left join ""Tasks"" as Tsk on DR.""TaskId""=Tsk.""Id"" 
                    left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                    left join ""SensitiveAccounts"" as SAcc on DR.""SensitiveAccountId""=SAcc.""Id""
                    left join ""DataRecordKeywords"" as DRK on DR.""Id""=DRK.""DataRecordId""
                    where DR.""StatusToken"" != 0 ";
                string searclTotalCountSql =
                    @"select count(distinct DR.""Id"") from ""DataRecords"" as DR 
                        left join ""Hosts"" as Hst on DR.""HostId""=Hst.""Id"" 
                        left join ""Tasks"" as Tsk on DR.""TaskId""=Tsk.""Id"" 
                        left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                        left join ""SensitiveAccounts"" as SAcc on DR.""SensitiveAccountId""=SAcc.""Id""
                        left join ""DataRecordKeywords"" as DRK on DR.""Id""=DRK.""DataRecordId""
                        where DR.""StatusToken"" != 0 ";
                string whereSql =
                    GenerateSearchWhere(multiSearch, hostIds) +
                    GenerateWhere(drFilter);
                searchSql += whereSql;
                if (!string.IsNullOrWhiteSpace(drFilter.OrderField) && !string.IsNullOrWhiteSpace(drFilter.Order))
                    searchSql += string.Format(" order by \"{0}\" {1}", drFilter.OrderField, drFilter.Order);
                if (!drFilter.IsTakeAll)
                    searchSql += " limit :pageSize offset :skipCount";
                int skipCount = pageSize*(pageIndex - 1);
                skipCount = skipCount < 0 ? 0 : skipCount;
                var dynamicParams = new
                {
                    drFilter.TaskId, pageSize,
                    skipCount,
                    drFilter.HostId, drFilter.Protocol,
                    drFilter.Email, drFilter.AccountId,
                    drFilter.KeywordId, drFilter.CaseId,
                    drFilter.Keyword, drFilter.HostMac
                };
                var dataRecords = db.SqlList<DataRecordResult>(searchSql, dynamicParams);
                
                searclTotalCountSql += whereSql;
                var totalCountInDb = db.QueryScalar<int?>(searclTotalCountSql, dynamicParams);
                totalCount = totalCountInDb.HasValue ? totalCountInDb.Value : 0;
                return dataRecords;
            }
        }

        private string GenerateSearchWhere(MultiSearch multiSearch, long[] hostIds)
        {
            string whereSql = string.Empty;
            const string drAlias = "DR", hostAlias = "Hst", accAlias = "SAcc";
            if (multiSearch != null)
            {
                if (!string.IsNullOrWhiteSpace(multiSearch.Subject))
                    whereSql += string.Format(" and {0}.\"Title\" like {{0}}", drAlias).Params(string.Format("%{0}%", multiSearch.Subject));
                if (multiSearch.StartDate.HasValue)
                    whereSql +=
                        string.Format(" and {0}.\"CreatedTime\" >= {{0}}", drAlias).Params(multiSearch.StartDate.Value);
                if(multiSearch.EndDate.HasValue)
                    whereSql +=
                        string.Format(" and {0}.\"CreatedTime\" <= {{0}}", drAlias).Params(multiSearch.EndDate.Value);
                if (!string.IsNullOrWhiteSpace(multiSearch.Protocol))
                    whereSql += string.Format(" and {0}.\"Protocol\" = {{0}}", drAlias).Params(multiSearch.Protocol);
                if (!string.IsNullOrWhiteSpace(multiSearch.RemoteIP))
                    whereSql += string.Format(" and {0}.\"RemoteIP\" = {{0}}", drAlias).Params(multiSearch.RemoteIP);
                if (!string.IsNullOrWhiteSpace(multiSearch.RemoteMac))
                    whereSql += string.Format(" and {0}.\"RemoteMac\" = {{0}}", drAlias).Params(multiSearch.RemoteMac);
                if (!string.IsNullOrWhiteSpace(multiSearch.EmailCC))
                    whereSql += string.Format(" and {0}.\"MailCC\" = {{0}}", drAlias).Params(multiSearch.EmailCC);
                if (!string.IsNullOrWhiteSpace(multiSearch.EmailTo))
                    whereSql += string.Format(" and {0}.\"MailTo\" = {{0}}", drAlias).Params(multiSearch.EmailTo);
                if (!string.IsNullOrWhiteSpace(multiSearch.EmailFrom))
                    whereSql += string.Format(" and {0}.\"MailFrom\" = {{0}}", drAlias).Params(multiSearch.EmailFrom);
                //todo:数据库时间计算问题，应该在between之间
                if (multiSearch.EmailDate.HasValue)
                    whereSql += 
                        string.Format(" and {0}.\"MailDate\" = {{0}}", drAlias).Params(multiSearch.EmailDate.Value);
                if (!string.IsNullOrWhiteSpace(multiSearch.SystemInfo))
                    whereSql += 
                        string.Format(" and {0}.\"SystemInfo\" = {{0}}", drAlias).Params(multiSearch.SystemInfo);
                if (!string.IsNullOrWhiteSpace(multiSearch.ClientVer))
                    whereSql += 
                        string.Format(" and {0}.\"SoftvareVer\" = {{0}}", drAlias).Params(multiSearch.ClientVer);
                if (!string.IsNullOrWhiteSpace(multiSearch.FileType))
                    whereSql +=
                        string.Format(" and {0}.\"FileType\" like {{0}}", drAlias).Params(string.Format("%{0}%", multiSearch.FileType));
                if (!string.IsNullOrWhiteSpace(multiSearch.Url))
                    whereSql +=
                        string.Format(" and {0}.\"Url\" like {{0}}", drAlias).Params(string.Format("%{0}%", multiSearch.Url));

                if (!string.IsNullOrWhiteSpace(multiSearch.Account))
                    whereSql +=
                        string.Format(" and {0}.\"Account\" like {{0}}", accAlias).Params(string.Format("%{0}%", multiSearch.Account));
                if (!string.IsNullOrWhiteSpace(multiSearch.Password))
                    whereSql +=
                        string.Format(" and {0}.\"Password\" like {{0}}", accAlias).Params(string.Format("%{0}%", multiSearch.Password));

                if(!string.IsNullOrWhiteSpace(multiSearch.IP))
                    whereSql += string.Format(" and {0}.\"IP\" = {{0}}", hostAlias).Params(multiSearch.IP);
                if (!string.IsNullOrWhiteSpace(multiSearch.Mac))
                    whereSql += string.Format(" and {0}.\"Mac\" = {{0}}", hostAlias).Params(multiSearch.Mac);
            }
            if (hostIds != null && hostIds.Any())
            {
                whereSql += string.Format(" and {0}.\"HostId\" in ({1})", drAlias, string.Join(",", hostIds));
            }

            return whereSql;
        }
        
        public IList<DataRecordResult> GetDataRecords(params long[] dataRecordIds)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select Hst.""HostName"",Hst.""IP"" as ""HostIP"",Hst.""Mac"" as ""HostMac"",
                    Tsk.""Name"" as ""TaskName"",Cases.""Name"" as ""CaseName"",
                    SAcc.""Account"",SAcc.""Password"",DR.* from ""DataRecords"" as DR 
                    left join ""Hosts"" as Hst on DR.""HostId""=Hst.""Id"" 
                    left join ""Tasks"" as Tsk on DR.""TaskId""=Tsk.""Id"" 
                    left join ""Cases"" as Cases on Tsk.""CaseId""=Cases.""Id""
                    left join ""SensitiveAccounts"" as SAcc on DR.""SensitiveAccountId""=SAcc.""Id""
                    where DR.""StatusToken"" != 0 ";
                string whereSql =
                    string.Format(" and DR.\"Id\" in ({0})", string.Join(",", dataRecordIds));
                searchSql += whereSql;
                var dataRecords = db.SqlList<DataRecordResult>(searchSql);
                return dataRecords;
            }
        } 

        public IList<DataRecord> GetDataRecords(long caseId, string whereSql = "")
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select DR.* from ""DataRecords"" as DR 
                        left join ""Tasks"" as Tsk on DR.""TaskId""=Tsk.""Id""
                        left join ""Cases"" as Cases on Tsk.""Id""=Cases.""Id""
                        where DR.""StatusToken"" != 0 and Cases.""Id"" = :caseId ";
                if (!string.IsNullOrWhiteSpace(whereSql))
                    searchSql += " " + whereSql;
                var dataRecords = db.SqlList<DataRecord>(searchSql, new { caseId });
                return dataRecords;
            }
        } 
 
        public ServiceResult ClearDataRecords(long caseId)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string updateSql =
                    @"update ""DataRecords"" set ""StatusToken"" = 0,""LastModifiedTime""=current_timestamp
                        where ""StatusToken"" != 0 and ""TaskId"" in 
                        (select ""Id"" from ""Tasks"" as Tsk where 
                            Tsk.""StatusToken"" != 0 and Tsk.""CaseId"" = {0})";
                return db.ExecuteSql(updateSql.Params(caseId)) > 0
                           ? ServiceResult.SuccessResult
                           : ServiceResult.UpdateFailureResult;
            }
        }

        public DataRecordKeyword GetDataRecordKeyword(long dataRecordId, long keywordId)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                var drk =
                    db.SingleOrDefault<DataRecordKeyword>("\"DataRecordId\"={0} and \"KeywordId\"={1}".Params(dataRecordId, keywordId));
                return drk;
            }
        }
 
        public ServiceResult SaveDataRecordKeywords(IList<DataRecordKeyword> dataRecordKeywords)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                db.SaveAll(dataRecordKeywords);
            }
            return ServiceResult.SuccessResult;
        }

        public IList<DataRecordAction> GetDataRecordActions(long dataRecordId)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                return
                    db.Select<DataRecordAction>(p => p.StatusToken != 0 && p.DataRecordId == dataRecordId);
            }
        }
 
        public IList<DataRecordAction> GetDataRecordActions(long[] drIds)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select * from ""DataRecordActions"" as DRA
                        where DRA.""StatusToken""!=0 and DRA.""DataRecordId"" in ({0})";
                searchSql = string.Format(searchSql, (drIds == null || !drIds.Any()) ? "-1" : string.Join(",", drIds));
                return db.SqlList<DataRecordAction>(searchSql);
            }
        } 

        public ServiceResult DeleteByIds(long[] dataRecordIds)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                var filePaths = new List<string>();
                using (var trans = db.OpenTransaction())
                {
                    try
                    {
                        foreach (long dataRecordId in dataRecordIds)
                        {
                            var dataRecord = db.Id<DataRecord>(dataRecordId);
                            if (dataRecord == null) continue;
                            dataRecord.StatusToken = 0;
                            db.UpdateOnly(dataRecord, p => p.StatusToken, p => p.Id == dataRecordId);
                            if (!string.IsNullOrWhiteSpace(dataRecord.FilePath))
                                filePaths.Add(dataRecord.FilePath);
                        }
                    }
                    catch (Exception ex)
                    {
                        //todo:log the save exception
                        trans.Rollback();
                        return new ServiceResult { ExecutedSuccess = false, Message = ex.Message };
                    }
                    trans.Commit();
                }
                return new ServiceResult {ExecutedSuccess = true, ReturnObj = filePaths};
            }
        }

        public IDictionary<string, long> GetProtocolRecordCount(long taskId)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                const string searchSql =
                    @"select ""Protocol"",count(""Protocol"") from ""DataRecords"" 
                        where ""StatusToken"" != 0 and ""TaskId"" = {0}
                        group by ""Protocol""";
                var protocolRecordDic = db.Dictionary<string, long>(searchSql.Params(taskId));
                return protocolRecordDic;
            }
        }

        public IList<RecordGroup> GroupByMac(long taskId, string protocol = null)
        {
            using (var db = DbFactory.OpenDbConnection())
            {
                string searchSql =
                    @"select Hst.""Mac"" as ""HostMac"",count(Hst.""Mac"") as ""Count"" 
                        from ""DataRecords"" as DR
                        left join ""Hosts"" as Hst on DR.""HostId""=Hst.""Id""
                        where DR.""StatusToken"" != 0 and DR.""TaskId"" = :taskId "
                    +
                    (string.IsNullOrWhiteSpace(protocol)
                         ? string.Empty
                         : @" and DR.""Protocol""=:protocol")
                    + @" group by Hst.""Mac""";
                return db.SqlList<RecordGroup>(searchSql, new {taskId, protocol});
            }
        }
    }
}